﻿using DBToExcel.Model;
using System;
using System.Collections.Generic;
using System.Data;
using Dapper;
using System.IO;

namespace DBToExcel.Code
{
    public class ExcelBuilderService
    {
        /// <summary>
        /// 查询数据
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public static MTempAllInfo QueryAllInfo(string id)
        {
            using (IDbConnection conn = SQLiteHelper.Current.GetConn())
            {
                ExcelBuilderDAL dal = new ExcelBuilderDAL();
                MTempAllInfo model = new MTempAllInfo();
                model.Map = dal.QueryExcelMapById(conn, id);
                if (model.Map != null && model.Map.Count > 0)
                {
                    foreach (var item in model.Map)
                    {
                        if (!string.IsNullOrEmpty(item.Script))
                        {
                            item.Script = Base64.Base64Decode(item.Script);
                        }
                    }
                }
                model.Template = dal.QueryTemplateById(conn, id);
                return model;
            }
        }

        /// <summary>
        /// 查询数据
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public static List<MTemplate> QueryTempLates(string name)
        {
            using (IDbConnection conn = SQLiteHelper.Current.GetConn())
            {
                ExcelBuilderDAL dal = new ExcelBuilderDAL();

                return dal.QueryTemplate(conn, name);
            }
        }

        /// <summary>
        /// 保存
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public static string Save(MTempAllInfo model)
        {
            IDbConnection conn = null;
            IDbTransaction trans = null;
            string tempid = string.Empty;
            FileStream fileStream = null;
            try
            {
                conn = SQLiteHelper.Current.GetConn();
                trans = conn.BeginTransaction();
                ExcelBuilderDAL dal = new ExcelBuilderDAL();

                //保存文件 
                string orgPath = model.Template.TempPlateExcelFile;
                if (model.Template.IsTempLate == 1)
                {
                    fileStream = File.Open(orgPath, FileMode.Open);
                    string filePath = FileConfig.SaveExcelTempLate(fileStream);
                    model.Template.TempPlateExcelFile = filePath;
                    fileStream.Close();
                }
                else
                {
                    model.Template.TempPlateExcelFile = string.Empty;
                    model.Template.OrgFileName = string.Empty;
                    model.Template.StartRowIndex = 0;
                }

                if (!string.IsNullOrEmpty(model.Template.Id))
                {
                    if (!dal.UpdateTemplate(conn, trans, model.Template))
                    {
                        throw new Exception("更新失败");
                    }

                }
                else
                {
                    model.Template.Id = Guid.NewGuid().ToString();

                    if (!dal.AddTemplate(conn, trans, model.Template))
                    {
                        throw new Exception("添加失败");
                    }
                }

                dal.DeleteExcelMapsByTId(conn, trans, model.Template.Id);

                if (model.Map != null && model.Map.Count > 0)
                {
                    foreach (var item in model.Map)
                    {
                        item.Id = Guid.NewGuid().ToString();
                        item.TemplateId = model.Template.Id;
                        item.Script = Base64.Base64Code(item.Script);
                    }

                    dal.AddExcelMaps(conn, trans, model.Map);
                }

                trans.Commit();
                tempid = model.Template.Id;

                //清理模板
                FileConfig.CLearTmp();
            }
            catch (Exception ex)
            {
                trans.Rollback();
                throw ex;
            }
            finally
            {
                if (conn != null)
                {
                    conn.Close();
                    conn.Dispose();
                }

                if (fileStream != null && fileStream.CanRead)
                {
                    fileStream.Close();
                }
            }

            return tempid;
        }

        /// <summary>
        /// 删除模板
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public static bool DeleteTempLate(string id)
        {
            IDbConnection conn = null;
            IDbTransaction trans = null;
            bool issuccess = false;
            try
            {
                conn = SQLiteHelper.Current.GetConn();
                trans = conn.BeginTransaction();
                ExcelBuilderDAL dal = new ExcelBuilderDAL();
                dal.DeleteTemplate(conn, trans, id);
                dal.DeleteExcelMapsByTId(conn, trans, id);
                trans.Commit();
                issuccess = true;
            }
            catch (Exception ex)
            {
                trans.Rollback();
                throw ex;
            }
            finally
            {
                if (conn != null)
                {
                    conn.Close();
                    conn.Dispose();
                }
            }

            return issuccess;
        }

        /// <summary>
        /// 保存连接
        /// </summary>
        /// <param name="conn"></param>
        /// <returns></returns>
        public static bool SaveDBConn(MDBConn conn)
        {
            using (IDbConnection connection = SQLiteHelper.Current.GetConn())
            {
                ExcelBuilderDAL dal = new ExcelBuilderDAL();
                if (string.IsNullOrEmpty(conn.Id))
                {
                    conn.Id = Guid.NewGuid().ToString();
                    return dal.AddDBConn(connection, conn);
                }
                else
                {
                    return dal.UpdateDBConn(connection, conn);
                }
            }
        }

        /// <summary>
        /// 查询全部连接
        /// </summary>
        /// <returns></returns>
        public static List<MDBConn> QueryAllMDBConns()
        {
            using (IDbConnection connection = SQLiteHelper.Current.GetConn())
            {
                ExcelBuilderDAL dal = new ExcelBuilderDAL();
                return dal.QueryAllDBConns(connection);
            }
        }

        public static DataTable QueryDataTable(IDbConnection connection, string sql)
        {
            using (connection)
            {
                DataTable dt = new DataTable();
                DataColumnCollection columnCollections = dt.Columns;
                IDataReader reader = connection.ExecuteReader(sql,commandTimeout:1000*60*5);
                //设置所有都可以为空
                foreach (DataRow item in reader.GetSchemaTable().Rows)
                {
                    DataColumn column = dt.Columns.Add(item["ColumnName"].ToString());
                    column.AllowDBNull = true;
                }

                while (reader.Read())
                {
                    DataRow row = dt.Rows.Add();
                    foreach (DataColumn item in dt.Columns)
                    {
                        row[item.Ordinal] = reader.GetValue(item.Ordinal);
                    }
                }


                //dt.Load(reader, LoadOption.Upsert);
                return dt;
            }
        }

        /// <summary>
        /// 删除连接
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public static bool DeleteDBConn(string id)
        {
            using (IDbConnection connection = SQLiteHelper.Current.GetConn())
            {
                ExcelBuilderDAL dal = new ExcelBuilderDAL();
                return dal.DeleteDBConn(connection, id);
            }
        }
    }
}
